package com.kuhh.dao.impl;

import com.kuhh.dao.IElectiveStudentDao;
import com.kuhh.pojo.ElectiveStudent;
import com.kuhh.pojo.ProfessionCourse;
import com.kuhh.pojo.Students;
import com.kuhh.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ElectiveStudentDaoImpl implements IElectiveStudentDao {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @Override
    public List<Students> getElectiveStudentListByCourseNo(String courseNo) {
        List<Students> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select ts.* from tb_students ts left join tb_elective_student tes on ts.student_no = tes.student_no where tes.course_no = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,courseNo);
            rs = ps.executeQuery();
            while (rs.next()){
                Students student = new Students();
                student.setStudentNo(rs.getString(1));
                student.setStudentName(rs.getString(2));
                student.setGradeNo(rs.getString(3));
                student.setCollegeNo(rs.getString(4));
                student.setProfessionNo(rs.getString(5));
                student.setClassNo(rs.getString(6));
                student.setDescription(rs.getString(7));
                student.setIdCard(rs.getString(8));
                student.setAge(rs.getInt(9));
                student.setGender(rs.getByte(10));
                student.setYear(rs.getString(11));
                student.setCreateTime(rs.getDate(12));
                student.setUpdateTime(rs.getDate(13));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<ProfessionCourse> getElectiveStudentListByStudentNo(String studentNo) {
        List<ProfessionCourse> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select tpc.* from tb_profession_course tpc left join tb_elective_student tes on tpc.course_no = tes.course_no where tes.student_no = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentNo);
            rs = ps.executeQuery();
            while (rs.next()){
                ProfessionCourse professionCourse = new ProfessionCourse();
                professionCourse.setCourseNo(rs.getString(1));
                professionCourse.setCourseName(rs.getString(2));
                professionCourse.setCourseType(rs.getString(3));
                professionCourse.setCourseCredit(rs.getString(4));
                professionCourse.setCourseNum(rs.getInt(5));
                professionCourse.setGradeNo(rs.getString(6));
                professionCourse.setProfessionNo(rs.getString(7));
                professionCourse.setTeacherNo(rs.getString(8));
                professionCourse.setCreateTime(rs.getTime(9));
                professionCourse.setUpdateTime(rs.getTime(10));
                list.add(professionCourse);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<ProfessionCourse> getElectiveUnNum() {
        List<ProfessionCourse> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select tpc.* " +
                    "from tb_profession_course tpc,(select course_no,count(*) num from tb_elective_student group by course_no) p " +
                    "where tpc.course_type != '1' and tpc.course_no = p.course_no and (tpc.course_num is null or p.num < tpc.course_num)";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                ProfessionCourse professionCourse = new ProfessionCourse();
                professionCourse.setCourseNo(rs.getString(1));
                professionCourse.setCourseName(rs.getString(2));
                professionCourse.setCourseType(rs.getString(3));
                professionCourse.setCourseCredit(rs.getString(4));
                professionCourse.setCourseNum(rs.getInt(5));
                professionCourse.setGradeNo(rs.getString(6));
                professionCourse.setProfessionNo(rs.getString(7));
                professionCourse.setTeacherNo(rs.getString(8));
                professionCourse.setCreateTime(rs.getTime(9));
                professionCourse.setUpdateTime(rs.getTime(10));
                list.add(professionCourse);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public ElectiveStudent getElectiveStudentById(Integer id) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,profession_no,course_no,student_no,create_time,update_time from tb_elective_student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                ElectiveStudent electiveStudent = new ElectiveStudent();
                electiveStudent.setId(rs.getInt(1));
                electiveStudent.setProfessionNo(rs.getString(2));
                electiveStudent.setCourseNo(rs.getString(3));
                electiveStudent.setStudentNo(rs.getString(4));
                electiveStudent.setCreateTime(rs.getTime(5));
                electiveStudent.setUpdateTime(rs.getTime(6));
                return electiveStudent;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return null;
    }

    @Override
    public boolean updateElectiveStudent(ElectiveStudent electiveStudent) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "update tb_elective_student set profession_no = ?, course_no = ?,student_no = ?,update_time = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,electiveStudent.getProfessionNo());
            ps.setString(2,electiveStudent.getCourseNo());
            ps.setString(3,electiveStudent.getStudentNo());
            ps.setDate(4, new Date(electiveStudent.getUpdateTime().getTime()));
            ps.setInt(5,electiveStudent.getId());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public boolean addElectiveStudent(ElectiveStudent electiveStudent) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into tb_elective_student(profession_no,course_no,student_no,create_time,update_time) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,electiveStudent.getProfessionNo());
            ps.setString(2,electiveStudent.getCourseNo());
            ps.setString(3,electiveStudent.getStudentNo());
            ps.setDate(4, new Date(electiveStudent.getCreateTime().getTime()));
            ps.setDate(5, new Date(electiveStudent.getUpdateTime().getTime()));
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public boolean deleteElectiveStudentById(Integer id) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "delete from tb_elective_student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public List<ElectiveStudent> getElectiveListByTeacherNo(String studentTeacherNo) {
        List<ElectiveStudent> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select es.* from tb_elective_student es left join tb_profession_course pc on es.course_no = pc.course_no where pc.teacher_no = ? order by es.create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentTeacherNo);
            rs = ps.executeQuery();
            while (rs.next()){
                ElectiveStudent electiveStudent = new ElectiveStudent();
                electiveStudent.setId(rs.getInt(1));
                electiveStudent.setProfessionNo(rs.getString(2));
                electiveStudent.setCourseNo(rs.getString(3));
                electiveStudent.setStudentNo(rs.getString(4));
                electiveStudent.setCreateTime(rs.getTime(5));
                electiveStudent.setUpdateTime(rs.getTime(6));
                list.add(electiveStudent);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }
}
